﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;

namespace CNotebook.Purchases
{
    public class GoodsReceivingReportManager:ConnectionManager
    {

        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPDN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Add/Update Order
        public void AddGRPO(GoodsReceivingUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OPDN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, CardCode, CardName, ContactPerson, Reference, RequestedBy, Department, BillTo, ShipTo, Remarks, SupplierInvoice, WithHoldingTax, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @CardCode, @CardName, @ContactPerson, @Reference, @RequestedBy, @Department, @BillTo, @ShipTo, @Remarks, @SupplierInvoice, @WithHoldingTax, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@SupplierInvoice", oUnit.SupplierInvoice));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));


            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));



            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                if (oRow["ExpiryDate"] != DBNull.Value)
                {
                    if (oRow["MfrDate"] != DBNull.Value)
                    {
                        oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, MfrDate, ExpiryDate, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @MfrDate, @ExpiryDate, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                    }
                    else
                    {
                        oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, ExpiryDate, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @ExpiryDate, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                    }
                }
                else
                {
                    if (oRow["MfrDate"] != DBNull.Value)
                    {

                        oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, MfrDate, LotNumber, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @MfrDate, @LotNumber, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                    }
                    else
                    {
                        oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                    }
                }


                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                if (oRow["ExpiryDate"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", Convert.ToDateTime(oRow["ExpiryDate"])));
                }

                if (oRow["MfrDate"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@MfrDate", Convert.ToDateTime(oRow["MfrDate"])));
                }
               
                oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                if (oRow["IsFree"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                }
                else
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                }
                oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                oCommand.ExecuteNonQuery();

            }
        }

        public void UpdateGRPO(GoodsReceivingUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPDN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, RequestedBy=@RequestedBy, Department=@Department, BillTo=@BillTo, ShipTo=@ShipTo, Remarks=@Remarks, SupplierInvoice=@SupplierInvoice, GrossAmount=@GrossAmount, WithHoldingTax=@WithHoldingTax, VATAmount=@VATAmount, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));

            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));

            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@SupplierInvoice", oUnit.SupplierInvoice));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE PDN1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    if (oRow["ExpiryDate"] != DBNull.Value)
                    {
                        if (oRow["MfrDate"] != DBNull.Value)
                        {
                            oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, MfrDate, ExpiryDate, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                            "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @MfrDate, @ExpiryDate, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                        }
                        else
                        {
                            oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, ExpiryDate, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                            "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @ExpiryDate, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                        }
                    }
                    else
                    {
                        if (oRow["MfrDate"] != DBNull.Value)
                        {

                            oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, MfrDate, LotNumber, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                            "(@DocNum, @ItemCode, @ItemDesc, @UoM, @MfrDate, @LotNumber, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                        }
                        else
                        {
                            oCommand.CommandText = "INSERT INTO PDN1 (DocNum, ItemCode, ItemDesc, UoM, LotNumber, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                            "(@DocNum, @ItemCode, @ItemDesc, @UoM, @LotNumber, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";
                        }
                    }


                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));

                    if (oRow["ExpiryDate"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", Convert.ToDateTime(oRow["ExpiryDate"])));
                    }

                    if (oRow["MfrDate"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@MfrDate", Convert.ToDateTime(oRow["MfrDate"])));
                    }
               

                    if (oRow["Qty"] == DBNull.Value)
                    {
                        int _ZeroValue = 0;
                        oRow["Qty"] = _ZeroValue;
                    }
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    oCommand.ExecuteNonQuery();

                }

            }
        }

        public SqlException AddDistributionEntries(string DocumentNumber, DataTable dtAccountingEntries)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                #region Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [PDN2] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in dtAccountingEntries.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandType = CommandType.Text;

                        oCommand.CommandText = "INSERT INTO [PDN2] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public void SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPDN SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT));

            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Post/Cancel/Close Order
        public void PostGRPO(string _WAREHOUSE, string _DOCNUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPDN SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.ExecuteNonQuery();


            #region Adding to Stocks
            string selectSQL = "SELECT opdn.CardCode, OPDN.CardName, PDN1.* FROM PDN1 LEFT JOIN OPDN ON OPDN.DocNum = PDN1.DocNum WHERE PDN1.DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                ItemManager oItemMaster = new ItemManager();
                StockManager oManager = new StockManager();
                
                oManager.Connection = this.Connection;
                oItemMaster.Connection = this.Connection;
                
                if (oItemMaster.IsForQuarantine(oRow["ItemCode"].ToString()) == false)
                {
                    if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                    {
                        oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["Qty"].ToString(), oRow["UnitPrice"].ToString());
                    }
                    else
                    {
                        WarehouseManager oWhs = new WarehouseManager();
                        oWhs.ConnectionString = this.ConnectionString;
                        oWhs.Open();
                        DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                        oWhs.Close();
                        oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), oRow["Qty"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitPrice"].ToString(), oRow["CardCode"].ToString(), oRow["CardName"].ToString());
                    }
                }
                else
                {
                    SqlCommand _COMMAND_QUARANTINE_ITEMS = new SqlCommand();
                    string _QUARANT_SQL = "INSERT INTO OQAITM SELECT " +
                                            "OPDN.DocNum, " +
                                            "OPDN.Location, " +
                                            "(SELECT WAREHOUSE.Dscription FROM WAREHOUSE WHERE WAREHOUSE.Code = OPDN.Location) as LocationName, " +
                                            "OPDN.CardCode, " +
                                            "OPDN.CardName, " +
                                            "pdn1.ItemCode, " +
                                            "pdn1.ItemDesc, " +
                                            "pdn1.Qty, " +
                                            "pdn1.UoM, " +
                                            "PDN1.UnitPrice, " +
                                            "PDN1.LotNumber, " +
                                            "PDN1.MfrDate, " +
                                            "PDN1.ExpiryDate, " +
                                            "PDN1.Amount, " +
                                            "'Quarantined' " +
                                        "from OPDN join PDN1 on opdn.DocNum = PDN1.DocNum WHERE OPDN.DocNum = @DOCNUM AND PDN1.ItemGuid=@ItemGuid";

                    _COMMAND_QUARANTINE_ITEMS.Connection = this.Connection;
                    _COMMAND_QUARANTINE_ITEMS.CommandText = _QUARANT_SQL;
                    _COMMAND_QUARANTINE_ITEMS.Parameters.Add(new SqlParameter("@DOCNUM", _DOCNUMBER));
                    _COMMAND_QUARANTINE_ITEMS.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    _COMMAND_QUARANTINE_ITEMS.ExecuteNonQuery();

                }

            }

            #endregion

        }

        public void CancelGRPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPDN SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void CloseGRPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPDN SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Data

        public DataTable GetAccountingDistribution(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM PDN2 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetGRPOBySupplier(string _CardName)
        {
            _CardName = '%' + _CardName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPDN WHERE CardName LIKE @CardName ORDER BY POSTINGDATE DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetGRPOs(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPDN WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetGRPOs(string _CardCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OPDN WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedReceivingReport(string _CardCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount Balance FROM OPDN WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DOCSTATUS='Posted' ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataRow GetGRPO(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPDN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }
        
        public DataTable GetGRPO(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            _DocNumber = '%' + _DocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OPDN WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetOpenAndPostedGRPO(string _CardCode, string _DocNumber)
        {
            _DocNumber = '%' + _DocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OPDN WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND (DocStatus='Posted' Or DocStatus='Open' Or DocStatus='Printed') ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public string GetGRPONumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocNum FROM OPDN WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count == 0)
            {
                return "";
            }
            else
            {
                DataRow oRow = ds.Tables[0].Rows[0];
                return oRow["DocNum"].ToString();
            }

        }

        public DataTable GetGRPOLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PDN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Report Retreivable
        //------------------------------------------------------------------
        //  HEADER



        public DataTable GetRRHeadersByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_RR";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetGRPOStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_GRPO_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
